/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package queenb.web.importExcel.controller;

import au.com.bytecode.opencsv.CSVReader;
import java.io.*;
import java.lang.String;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.richfaces.event.UploadEvent;
import org.richfaces.model.UploadItem;
import queenb.web.bean.FileDescription;
import queenb.web.controller.QueenbController;
import queenb.web.importExcel.store.importStore;
import queenb.web.store.N750Store;
import queenb.web.util.Util;

/**
 *
 * @author Nguyen Hung
 */
public class importController extends QueenbController {

    private String pvNV102;
    private String pvNV103;
    private int pnNN104;
    private String pvNV108;
    private String pvNV109;
    private String pvNV112;
    private int pnNN117;
    private Date pdND118;
    private String pvNV119;
    private String pvNV122;
    private String pvNV130;
    private int pnNN131;
    private String pvNV351C;
    private String pvNV351P;
    private Date pdND352;
    private Date pdND353;
    private String pvNV354;
    private String pvKV301;
    private String pvKV103;
    private int pnFK350C;
    private int pnFK350V;
    private String pvNV552;
    private String pvNV251;
    private String pvNV952;
    private String pvNV955;
    private String pvNV957;
    private String pvNV958;
    private String pvNV959;
    private String pvNV960;
    private String pvNV961;
    private String pvNV962;
    private String pvNV964;
    private String pvNV967;
    private String pvNV968;
    private String pvNV969;
    private importStore importEx;
    private N750Store n750Store;
    private int flag = 0;
    private int isVendor = 0;
    //import quoc gia tinh thanh
    private String pvNV753;//chau luc
    private String pvNV754;//quoc gia
    private String pvAV001;//tinh thanh
    private int pnAN006;//so luong
    private int rs;

    public importController() {
        importEx = new importStore();
        n750Store = new N750Store();
        if (getRequest().getParameter("flag") != null) {
            flag = Integer.parseInt(getRequest().getParameter("flag").toString());
        }
        System.out.println("flag--" + flag + "--" + getRequest().getParameter("flag"));
    }

    public void uploadDocListener(UploadEvent ue) throws IOException {
        System.out.println("==++++++++++++++++++++++ start ");
        uploadAttachment(ue, FileDescription.DOC_KIND_OF_FILE);
    }

    private void uploadAttachment(UploadEvent ue, int typeUpload) {

        List<UploadItem> uploadItem = ue.getUploadItems();

        for (int i = 0; i < uploadItem.size(); i++) {
            try {
                UploadItem uploadItem1 = uploadItem.get(i);
                if (flag == 1) {
                    //import quoc gia tinh thanh
                    System.out.println("import quoc gia tinh thanh");
                    Util.saveFileXLS(uploadItem1);
                    importCity();
                } else {
                    //import khách hàng
                    System.out.println("import khách hang");
                    Util.saveFileCSV(uploadItem1);
                    importExcel();
                }


            } catch (Exception ex) {
                setMessages("ERROR:COULD NOT ATTACH FILE:CODE:" + ex.getMessage(), "ERROR:COULD NOT ATTACH FILE:CODE:" + ex.getMessage());
//                showGlobalReport(true, getResourceString("importFail"));
            }
//                sendRedirect("success.jsp");
        }
    }

    private void importCity() {
        try {
            File file = new File("D://SGT-QUEENB//upload//import.xls");
            HSSFWorkbook wb = null;
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
            Float f = new Float(0);
            wb = new HSSFWorkbook(fs);
            System.out.println("so luong sheet" + wb.getNumberOfSheets());
            //vong lap lay het cai sheet trong file excel
            for (int b = 0; b < wb.getNumberOfSheets(); b++) {
                HSSFSheet sheet = wb.getSheetAt(b);
                int numRow = sheet.getLastRowNum() + 1;
                System.out.println("---> so luong record--:" + numRow);
                //vong lap lay du lieu trong 1 sheet
                for (int i = 2; i < numRow; i++) {
                    if (!sheet.getRow(i).getCell(3).toString().isEmpty()) {
                        if (!sheet.getRow(i).getCell(1).toString().isEmpty()) {
                            pvNV753 = sheet.getRow(i).getCell(1).toString();//chau luc 
                        }
                        if (!sheet.getRow(i).getCell(2).toString().isEmpty()) {
                            pvNV754 = sheet.getRow(i).getCell(2).toString(); //quoc gia
                        }
                        if (!sheet.getRow(i).getCell(3).toString().isEmpty()) {
                            pvAV001 = sheet.getRow(i).getCell(3).toString();// tinh thanh
                        } else {
                            pvAV001 = null;
                        }
//                        if (!sheet.getRow(i).getCell(4).toString().isEmpty()) {
//                            f = Float.parseFloat(sheet.getRow(i).getCell(4).toString());
//                            pnAN006 =Integer.parseInt(String.valueOf(f.intValue()));//so luong
//                        }else{
                        pnAN006 = 0;
//                        }
                        System.out.println("---col--" + i + "IMPORTTABN750(" + pvNV753 + "','" + pvNV754 + "','" + pvAV001 + "','" + pnAN006);
                        rs = n750Store.IMPORTTABN750(pvNV753, pvNV754, pvAV001, pnAN006, getLogin().getUserName());
                        System.out.println("---col--" + i + "--rs--" + rs);

                    }
                }

            }
        } catch (Exception e) {
        }

    }

    private void importExcel() {
        try {
            File file = new File("D://SGT-QUEENB//upload//import.csv");
            BufferedReader bufRdr = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF-8"));
//            String line = null;
            String[] s = new String[51];
            int row = 0;
            System.out.println("begin read data");
            CSVReader reader = new CSVReader(bufRdr);
            while ((s = reader.readNext()) != null) {
                // nextLine[] is an array of values from the line
                row++;
                if (s[2] != null && !s[2].equals("") && row > 1) {
                    //Mr / ms
                    if (s[0] != null && !s[0].equals("")) {
                        pvNV108 = s[0];
                    } else {
                        pvNV108 = "Mr";
                    }
                    //-- ho 
                    if (s[1] != null && !s[1].equals("")) {
                        pvNV102 = s[1];
                    } else {
                        pvNV102 = "no name";
                    }
                    //-- ten
                    pvNV103 = s[2];
                    //-- tiem nang / than thiet 
                    pnNN104 = 1;
                    //tinh trang hon nhan
                    if (s[27].equals("Married")) {
                        pvNV109 = "1";
                    } else {
                        pvNV109 = "0";
                    }
                    //nv chiu trach nhiem
                    if (s[23] != null && !s[23].equals("")) {
                        pvNV112 = s[23];
                    } else {
                        pvNV112 = null;
                    }
                    // , `pnNN117` int           -- rating
//                    if (s[22] != null && !s[22].equals("")) {
//                        if (!s[22].contains("-")) {
////                            pnNN117 = Integer.parseInt(s[22].substring(0, 1));
//                            pnNN117 = 1;
//                        }
//                    } else {
//                        pnNN117 = 1;
//                    }
                    pnNN117 = 1;
//                                                 , `pdND118` date          -- ngay thang nam sinh
                    if (s[30] != null && !s[30].equals("")) {
                        try {
                            pdND118 = Util.parseDate(s[30], "MM/dd/yyyy");
                        } catch (Exception e) {
                            pdND118 = null;
                        }

                    } else {
                        pdND118 = null;
                    }
//                                                 , `pvNV119` VARCHAR(128)  -- ho khong dau
                    if (pvNV102 != null && !pvNV102.equals("")) {
                        pvNV119 = Util.UnUnicode(pvNV102);
                    } else {
                        pvNV119 = null;
                    }
//                                                 , `pvNV122` VARCHAR(128)  -- ten khong dau
                    if (pvNV103 != null && !pvNV103.equals("")) {
                        pvNV122 = Util.UnUnicode(pvNV103);
                    } else {
                        pvNV122 = null;
                    }
//                                                 , `pvNV130` text          -- ghi chu
                    pvNV130 = s[49];

//                                                 , `pnNN131` Int           -- do not call
                    if (s[17] != null && !s[17].equals("")) {
                        try {
                            pnNN131 = Integer.parseInt(s[17]);
                        } catch (Exception e) {
                            pnNN131 = 0;
                        }

                    }
//                                                 , `pvNV351C` VARCHAR(32)   -- so CMND 
                    pvNV351C = s[29];
//                                                 , `pvNV351P` VARCHAR(32)   -- so Pass
                    pvNV351P = s[31];
//                                                 , `pdND352` date          -- ngay cap
                    if (s[32] != null && !s[32].equals("")) {
                        try {
                            pdND352 = Util.parseDate(s[32], "MM/dd/yyyy");
                        } catch (Exception e) {
                            pdND352 = null;
                        }

                    } else {
                        pdND352 = null;
                    }
//                                                 , `pdND353` date          -- ngay het han
                    if (s[34] != null && !s[34].equals("")) {
                        try {
                            pdND353 = Util.parseDate(s[34], "MM/dd/yyyy");
                        } catch (Exception e) {
                            pdND353 = null;
                        }

                    } else {
                        pdND353 = null;
                    }
//                                                 , `pvNV354` VARCHAR(512)  -- noi cap
                    pvNV354 = s[33];
//                                                 -- 
//                                                 , `pvKV301` VARCHAR(128)  -- source
                    pvKV301 = s[24];
//                                                 , `pvKV103` VARCHAR(128)  -- ten Cty 
                    pvKV103 = s[3];
//                                                 , `pnFK350C` int           -- isCustomer
                    if (s[15] != null && !s[15].equals("")) {
                        try {
                            pnFK350C = Integer.parseInt(s[15]);
                        } catch (Exception e) {
                            pnFK350C = 0;
                        }

                    }
//                                                 , `pnFK350V` int           -- isVendor
                    if (s[16] != null && !s[16].equals("")) {
                        try {
                            pnFK350V = Integer.parseInt(s[16]);
                        } catch (Exception e) {
                            pnFK350V = 0;
                        }

                    }
//                                                 , `pvNV552` VARCHAR(256)  -- nganh nghe
                    if (s[30] != null && !s[30].equals("")) {
                        if (!s[21].contains("-")) {
                            pvNV552 = s[21];
                        } else {
                            pvNV552 = null;
                        }
                    } else {
                        pvNV552 = null;
                    }
//                                                 , `pvNV251` VARCHAR(64)   -- chuc vu
                    pvNV251 = s[4];
//                                                 , `pvNV952` VARCHAR(256)  -- ddia chi
                    pvNV952 = s[39];
//                                                 , `pvNV955` VARCHAR(256)  -- quan
                    pvNV955 = s[41];
//                                                 , `pvNV957` VARCHAR(256)  -- tinh thanh
                    pvNV957 = s[43];
//                         -- NV958: office phone
                    pvNV958 = s[9];
//   -- pvNV959: office extension
                    pvNV959 = s[10];
//   -- pvNV960: home phone (other home phone ko can import)
                    pvNV960 = s[11];
//   -- pvNV961: Mob1
                    if (s[5] != null && !s[5].equals("")) {
                        pvNV961 = "0" + s[5];
                    } else {
                        pvNV961 = null;
                    }
//   -- pvNV962: Mob2
                    pvNV962 = s[6];
//   -- pvNV964: FAX
                    pvNV964 = s[13];
//   -- pvNV967: eMail1
                    pvNV967 = s[7];
//   -- pvNV968: other email
                    pvNV968 = s[8];
//   -- pvNV969: nick skype
                    pvNV969 = s[14];
//                    System.out.println("STDN_TOOLS_IMPORTTABN100('" + pvNV102 + "','" + pvNV103 + "','" + pnNN104 + "','" + pvNV108 + "','" + pvNV109 + "','"
//                            + pvNV112 + "','" + pnNN117 + "','" + pdND118 + "','" + pvNV119 + "','" + pvNV122 + "','"
//                            + pvNV130 + "','" + pnNN131 + "','" + pvNV351C + "','" + pvNV351P + "','" + pdND352 + "','"
//                            + pdND353 + "','" + pvNV354 + "','" + pvKV301 + "','" + pvKV103 + "','" + pnFK350C + "','"
//                            + pnFK350V + "','" + pvNV552 + "','" + pvNV251 + "','" + pvNV952 + "','" + pvNV955 + "','"
//                            + pvNV957 + "','" + pvNV958 + "','" + pvNV959 + "','" + pvNV960 + "','" + pvNV961 + "','"
//                            + pvNV962 + "','" + pvNV964 + "','" + pvNV967 + "','" + pvNV968 + "','" + pvNV969 + "','"
//                            + getLogin().getModule() + "','" + getLogin().getCompanyNo() + "','" + getLogin().getUserName());
//                    System.out.println("------------------------------------------------------------------------------");
                    rs = importEx.importCustomer(pvNV102, pvNV103, pnNN104, pvNV108, pvNV109,
                            pvNV112, pnNN117, Util.toSQLDate(pdND118), pvNV119, pvNV122,
                            pvNV130, pnNN131, pvNV351C, pvNV351P, Util.toSQLDate(pdND352),
                            Util.toSQLDate(pdND353), pvNV354, pvKV301, pvKV103, pnFK350C,
                            pnFK350V, pvNV552, pvNV251, pvNV952, pvNV955,
                            pvNV957, pvNV958, pvNV959, pvNV960, pvNV961,
                            pvNV962, pvNV964, pvNV967, pvNV968, pvNV969,
                            getLogin().getModule(), getLogin().getCompanyNo(), getLogin().getUserName());
                    System.out.println("row-:" + row + "---pvNV103---" + pvNV103 + "---pvKV103---" + pvKV103 + "---isvendor" + pnFK350V + "--value----:" + rs);

                    if (pnFK350V > 0) {
                        isVendor++;
                    }
                }
            }

            System.out.println("end read data");
        } catch (Exception e) {
            System.out.println("erorr-----" + e.getMessage());
        } finally {
            System.out.println("isVendor-----" + isVendor);
        }
    }

    /**
     * @return the flag
     */
    public int getFlag() {
        return flag;
    }

    /**
     * @param flag the flag to set
     */
    public void setFlag(int flag) {
        this.flag = flag;
    }
}
